package com.data.modules.system.util;

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 引入 StreamingReader 支持excel大数量时的读取
 */
public class ExcelUtils {

    public static Map<String, List<String>> test(File file) throws Exception {
        FileInputStream in = new FileInputStream(file);
        Map<String, List<String>> mapData = new HashMap<String, List<String>>();
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(100)  //缓存到内存中的行数，默认是10
                .bufferSize(4096)  //读取资源时，缓存到内存的字节大小，默认是1024
                .open(in);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
        int sheetNums = wk.getNumberOfSheets();
        for(int i = 0 ; i < sheetNums;i ++){
            List<String> sheetData = new ArrayList<String>();
            Sheet sheet = wk.getSheetAt(i);
            String sheetName = wk.getSheetName(i);
            //遍历所有的行
            for (Row row : sheet) {
                StringBuilder sb = new StringBuilder();
                //遍历所有的列
                for (Cell cell : row) {
                    sb.append(cell.getStringCellValue()).append("A&D");
                }
                sheetData.add(sb.toString());
            }
            mapData.put(sheetName,sheetData);

        }
        return mapData;
    }

    public static void main(String[] args) throws Exception {
        File file = new File("D:\\tmp\\GSVA_Settlement_Log.xlsx");
        System.out.println("=====");
        Map<String, List<String>> test = test(file);
        for(Map.Entry<String,List<String>> m : test.entrySet()){
            for(String s : m.getValue()){
                System.out.println(s);
            }
        }

        String msg = "31/05/2021A&D07:54:16 AMA&DG0012115162366A&DSendA&DBanksA&D000000A&D10081975A&DCBIBA&DIDR 7094594.00 / 3443.00000000A&DMYRA&D-2,060.58A&D-103,841.78A&D-105,902.36A&DTransaction is SuccessfulA&D A&D A&DA&DA&DA&DA&D A&D";
        String[] list = msg.split("A&D");
        for(int i = 0;i < list.length;i ++){
            String v = list[i] ;
            System.out.println(i+":"+v);
        }
    }
}

